package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;

/**
 * Connection to emails table
 * 
 * Performs DAO operations for the Emails
 * @author b_aldosa
 * @since June 15, 2013
 * @version $Revision: 1.0 $
 */
public class EmailSqlDao implements EmailDao {
	
	// Connection
	private static Connection conn;
    
    /**
     * Constructor
     * @param conn connection object for database
     */
    public EmailSqlDao(Connection conn) {
		this.conn = conn;
	}
    
    /**
     * Creates a new email entry and returns the ID
     * @param properties
     * @return int
     * @throws DaoException
     * @throws ModelException  * @see dao.EmailDao#create(HashMap<String,String>)
     */
    public int create(HashMap<String, String> properties) throws DaoException{
    	
    	int toReturn = -1;
    	try {
    		String sql = "INSERT INTO emails (ToEmail, FromEmail, Subject, Body, Read, Ts, PendingRecepients) VALUES (?, ?, ?, ?, ?, ?,?) ;";

	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setString(1, properties.get("ToEmail"));
	    	statement.setString(2, properties.get("FromEmail"));
	    	statement.setString(3, properties.get("Subject"));
	    	statement.setString(4, properties.get("Body"));
	    	statement.setBoolean(5, Boolean.valueOf(properties.get("Read")));
	    	statement.setTimestamp(6, Timestamp.valueOf(properties.get("Ts")));
	    	statement.setString(7, properties.get("PendingRecepients"));
	    	statement.executeUpdate();	

	    	Statement st = conn.createStatement();
	    
        	ResultSet generatedKeys = st.executeQuery("SELECT seq FROM sqlite_sequence WHERE name = 'emails' ;");
        	if (generatedKeys.next()) {
        	    toReturn = generatedKeys.getInt("seq");
        	}	    		    
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	return toReturn;
    }
    
    /**
     * Loads a email row for given ID
    
    
    
     * @param Id int
     * @return HashMap<String,String>
     * @throws DaoException
     * @throws ModelException  * @see dao.EmailDao#load(int)
     */
    public HashMap<String, String> load(int Id) throws DaoException{
    	
    	HashMap<String, String> toReturn;
    	try {
	    	String sql = "SELECT * FROM emails WHERE Id = ? LIMIT 1; ";
	    	PreparedStatement statement = null; 
	    	statement = conn.prepareStatement(sql);
	    	statement.setInt(1, Id);
	    	ResultSet rs = statement.executeQuery();
	    	toReturn = new HashMap<String, String>();
	    	while (rs.next()) {
	    		toReturn.put("Id", String.valueOf(Id));    		
	    		toReturn.put("ToEmail", rs.getString("ToEmail"));
	    		toReturn.put("FromEmail", rs.getString("FromEmail"));
	    		toReturn.put("Subject", rs.getString("Subject"));
	    		toReturn.put("Body", rs.getString("Body"));
	    		toReturn.put("Read", rs.getString("Read"));
	    		toReturn.put("Ts", rs.getString("Ts"));
	    		toReturn.put("PendingRecepients", rs.getString("PendingRecepients"));
	    	}
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	return toReturn;
    }

	/**
	 * Deletes an email row, given the id
	
	
	 * @param Id int
	 * @throws DaoException
	 * @throws ModelException  * @see dao.EmailDao#delete(int)
	 */
	public void delete(int Id) throws DaoException{
		
		try {
			String sql = "DELETE FROM emails WHERE Id = ? ; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setInt(1, Id);
	    	statement.executeUpdate();
		} catch (SQLException e){
			throw new DaoException(e.getMessage());
		}
		
	}
	
	/**
	 * Loads all the emails
	 * @return
	 * @throws DaoException
	 */
	public HashMap<Integer, String[]> findAll() throws DaoException {
		
		HashMap<Integer, String[]> properties = new HashMap<>();
		
		int index = 0, key;
		try {
			PreparedStatement statement = conn.prepareStatement("SELECT * FROM emails ORDER BY FromEmail;");
	        ResultSet rs = statement.executeQuery();
	        while (rs.next())
	        {
	        	String folderProperties[] = new String[8];
	    		index = 0;
	    		key = rs.getInt(1);
	    		folderProperties[index++] = rs.getString(2);
	    		folderProperties[index++] = rs.getString(3);
	    		folderProperties[index++] = rs.getString(4);
	    		folderProperties[index++] = rs.getString(5);
	    		folderProperties[index++] = rs.getString(6);
	    		folderProperties[index++] = rs.getString(7);
	    		folderProperties[index++] = rs.getString(8);
	    		properties.put(key, folderProperties);        	                
	        }
	        rs.close();  
		} catch (SQLException e) {
			throw new DaoException(e.getMessage());
		} 
        return properties;
	}
	
	
	/**
	 * Updates an email object in the dao
	 */
   public void update(HashMap<String, String> properties) throws DaoException {

    	try {
    		String sql = "UPDATE emails SET ToEmail=?, FromEmail=?, Subject=?, Body=?, Read=?, Ts=?, PendingRecepients=?  WHERE Id = ?; ";
        	PreparedStatement statement = conn.prepareStatement(sql);
        	statement.setString(1, properties.get("ToEmail"));
        	statement.setString(2, properties.get("FromEmail"));
        	statement.setString(3, properties.get("Subject"));
        	statement.setString(4, properties.get("Body"));
        	statement.setBoolean(5, Boolean.valueOf(properties.get("Read")));
        	statement.setTimestamp(6, Timestamp.valueOf(properties.get("Ts")));
        	statement.setString(7, properties.get("PendingRecepients"));
        	statement.setInt(8, Integer.valueOf(properties.get("Id")));
        	statement.executeUpdate();
        	System.out.println("Finished update of email!");
        	
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    }

	
	/**
	 * Deletes all emails with the given IDs
	
	
	 * @param ids Set<Integer>
	 * @throws DaoException
	 * @throws ModelException  * @see dao.EmailDao#deleteById(Set<Integer>)
	 */
	public void deleteById(Set<Integer> ids) throws DaoException{
		try {
			String sql = "DELETE FROM emails WHERE Id IN ( "+ StringUtils.join(ids, " , ") +" ) ; ";
	    	Statement statement = conn.createStatement();
	    	statement.execute(sql);
		} catch (SQLException e){
			throw new DaoException(e.getMessage());
		}
	}

}